﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.IO;

namespace ExcelUtils
{
    public class ExcelSession : IDisposable
    {

        #region Private Members

        private Application _app;

        #endregion Private Members

        #region Public Properties

        public bool IsVisible
        {
            get
            {
                lock (this)
                {
                    return _app.Visible;
                }
            }
            set
            {
                lock (this)
                {
                    _app.Visible = value;
                }
            }
        }

        #endregion Public Properties

        #region Constructors/Desctrutor

        /// <summary>
        /// Creates an excel session via interop. The application is not shown.
        /// </summary>
        public ExcelSession()
        {
            _app = new Application();
            if (_app == null)
            {
                throw new ApplicationException("Unable to create an Excel session.");
            }
        }

        /// <summary>
        /// This ensures the session is cleaned up and closed down.
        /// </summary>
        ~ExcelSession()
        {
            this.Dispose();
        }

        #endregion Constructors

        #region Public Methods

        /// <summary>
        /// Opens the excel file (xla, xls, csv) in the excel session.
        /// </summary>
        /// <param name="excelFile">The existing excel file.</param>
        public void Open(FileInfo excelFile)
        {
            try
            {
                lock(this){
                    _app.Workbooks.Open(excelFile.FullName);
                }
            }
            catch (COMException e)
            {
                throw new ApplicationException("The excel file could not be opened in the session.", e);
            }
        }

        /// <summary>
        /// Runs the specified macro passing the parameters through in the same order.
        /// </summary>
        /// <param name="marcoName"></param>
        /// <param name="parameters"></param>
        public void RunMacro(String marcoName, params object[] parameters)
        {
            List<object> fullParams = new List<object>(31);
            fullParams.Add(marcoName);
            fullParams.AddRange(parameters);
            
            try
            {
                lock (this)
                {
                    _app.GetType().GetMethod("Run").Invoke(_app, fullParams.ToArray());
                }

            } catch (COMException e)
            {
                throw new ApplicationException("The macro " + marcoName + " could not be run.", e);
            }
        }

        
        /// <summary>
        /// Returns a list of Named ranges in the current workbook.
        /// </summary>
        /// <returns></returns>
        public List<String> GetNames()
        {
            List<String> names = new List<string>();
            try
            {
                lock (this)
                {
                    if (_app.ActiveWorkbook == null)
                    {
                        throw new ApplicationException("The must be an open workbook to fetch names.");
                    }
                    foreach (Name n in _app.Names)
                    {
                        names.Add(n.Name);
                    }
                }
            }
            catch (COMException e)
            {
                throw new ApplicationException("There was an error getting named ranges from excel.", e);
            }
            return names;
        }

        /// <summary>
        /// Gets all the data as a 2d array of objects. Warning this will be slow and is included for testing. 
        /// </summary>
        /// <returns></returns>
        public object[,] FetchAllData()
        {
            object[,] data;
            try
            {
                lock (this)
                {
                    Range range = _app.get_Range("A1", "EZ65536");
                    data = range.Value2;
                }
            }
            catch (COMException e)
            {
                throw new ApplicationException("There was an exception fetching data from excel.", e);
            }
            return data;
        }


        #region IDisposable methods
        
        /// <summary>
        /// Cleans up the excel session to ensure it closes.
        /// </summary>
        public void Dispose()
        {
            if (_app != null)
            {
                lock (this)
                {
                    _app.Quit();
                    Marshal.ReleaseComObject(_app);
                    _app = null;
                }
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }

        #endregion IDisposable methods

        #endregion Public Methods

        public void SetRange(string range, string value)
        {
            _app.Range[range].Value2 = value;

        }

        public void CreateNewWorkbook()
        {
            _app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
        }

        public string GetValueFromRange(string range)
        {
            return _app.Range[range].Value2.ToString();
        }

        public void Calculate()
        {
            _app.ActiveWorkbook.ActiveSheet.Calculate();
        }
    }
}
